////////////////////////////////////////////////////////////////////////////////
// Copyright 2007 CORE Solutions Limited
// All rights reserved.
// PROPRIETARY AND CONFIDENTIAL INFORMATION.
// Use is subject to license terms.
//
// FUNCTION ID      :   CATALOG_SECURITY
// AUTHOR           :   Saint Peng
// DATE CREATE      :   2007/03/29
// DESCRIPTION      :   a DAO file for the function, CATALOG_SECURITY
//                  :
//                  :
//                  :
//
// MODIFICATION HISTORY:
// DATE         AUTHOR          VERSION NO.         COMMENT
// -----------  --------------  ------------------  -------------------
// 2007/03/29   Saint Peng      1.0                 source is generated.
//
//
//
////////////////////////////////////////////////////////////////////////////////

package com.erp.app.catalog.catalogSecurity.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.erp.app.catalog.catalogSecurity.pk.CatalogSecurityFieldPk;
import com.erp.app.catalog.catalogSecurity.pk.CatalogSecurityHdrPk;
import com.erp.app.catalog.catalogSecurity.vo.CatalogSecurityFieldVo;
import com.erp.app.common.ProjectConstants;
import com.erp.framework.exceptions.FindRecordException;
import com.erp.framework.model.dao.SimpleSearchMaintStyleDao;
import com.erp.framework.model.vo.ChildList;
import com.erp.framework.model.vo.SimpleBaseVo;
import com.erp.framework.types.CustomBigInteger;
import com.erp.framework.util.CommonUtil;

/**
* <p>A <strong>DAO</strong>(Data Access Object) abstracts and encapsulates
* all access to the persistent store.  The DAO manages the connection with the
* data source to obtain and store data.  Also, the DAO implements
* the access mechanism required to work with the data source,
* and always provides a uniform API to its clients.
*
* @author          Saint Peng
* @version 1.0     2007/03/29
* @since           FrameWork 1.0
*
*/
public class CatalogSecurityFieldDao extends SimpleSearchMaintStyleDao {

	/**
	* Returns an array list of child VOs by the parent
	* @param         parent   the incoming parent VO
	* @param         conn     the database connection
	* @return        an array list of child VOs
	*
	* @author     Saint Peng
	* @since      Framework 1.0
	*/
	public ChildList selectByParent(SimpleBaseVo parent, Connection conn)
		throws FindRecordException {

		return super.selectByParent(parent, CatalogSecurityFieldVo.class, conn);
	}

	/**
	* Constructs a string of SQL insert statement
	* @return        a string of SQL insert statement
	*
	* @author     Saint Peng
	* @since      Framework 1.0
	*/
	protected String sqlInsert() {

		String sql = "INSERT INTO T_CATALOG_SECURITY_FIELD ("
			+ "COMPANY_CODE, USER_CODE, DTL_ID, NODE_ID, CREATE_USER, CREATE_DATE ) "
			+ "VALUES ("
			+ "?, ?, ?, ?, ?, "
			+ "SYSDATE )";
		return sql;
	}

	/**
	* Sets the values for a SQL insert or update statement.
	* @param         pStmt   the incoming prepared statement
	* @param         obj     the incoming VO
	*
	* @author     Saint Peng
	* @since      Framework 1.0
	*/
	protected void setValuesForInsertAndModify(PreparedStatement pStmt, Object obj, String opType)
		throws SQLException {

		CatalogSecurityFieldVo vo = (CatalogSecurityFieldVo) obj;
		int i = 1;
		pStmt.setString(i++, vo.getCompanyCode());
		pStmt.setString(i++, vo.getUserCode());
		pStmt.setInt(i++, CommonUtil.strToBigDecimal(vo.getFieldDtlId()).intValue());
		pStmt.setInt(i++, CommonUtil.strToBigDecimal(vo.getFieldNodeId()).intValue());
		pStmt.setString(i++, vo.getCreateUser());

		if(ProjectConstants.DB_OP_UPDATE.equals(opType)){
			setNullableTimestamp(pStmt, i++, vo.getCreateDate());
			pStmt.setString(i++, vo.getUpdateUser());
			pStmt.setString(i++, vo.getCompanyCode());
			pStmt.setString(i++, vo.getUserCode());
			pStmt.setInt(i++, CommonUtil.strToBigDecimal(vo.getFieldDtlId()).intValue());
			pStmt.setInt(i++, CommonUtil.strToBigDecimal(vo.getFieldNodeId()).intValue());

			setNullableTimestamp(pStmt, i++, vo.getUpdateDate());
		}
	}

	/**
	* Sets the values for a SQL insert statement.
	* @param         pStmt   the incoming prepared statement
	* @param         obj     the incoming VO
	*
	* @author     Saint Peng
	* @since      Framework 1.0
	*/
	protected void setValuesForInsert(PreparedStatement pStmt, Object obj)
		throws SQLException {

		setValuesForInsertAndModify(pStmt, obj, ProjectConstants.DB_OP_INSERT);
	}

	/**
	* Constructs a string of SQL update statement
	* @return        a string of SQL update statement
	*
	* @author     Saint Peng
	* @since      Framework 1.0
	*/
	protected String sqlUpdate() {

		String sql = "UPDATE T_CATALOG_SECURITY_FIELD SET COMPANY_CODE=?, USER_CODE=?, DTL_ID=?, NODE_ID=?, " +
				" CREATE_USER=?, CREATE_DATE=?, UPDATE_USER=?, UPDATE_DATE = sysdate"
			+ "WHERE company_code=? AND USER_CODE=? AND DTL_ID=? AND NODE_ID=? "
			+ "AND (UPDATE_DATE IS NULL OR UPDATE_DATE = ?) ";
		return sql;
	}

	/**
	* Sets the values for a SQL update statement.
	* @param         pStmt   the incoming prepared statement
	* @param         obj     the incoming VO
	*
	* @author     Saint Peng
	* @since      Framework 1.0
	*/
	protected void setValuesForModify(PreparedStatement pStmt, Object obj)
		throws SQLException {

		setValuesForInsertAndModify(pStmt, obj, ProjectConstants.DB_OP_UPDATE);
	}

	/**
	* Constructs a string of SQL delete statement
	* @return        a string of SQL update statement
	*
	* @author     Saint Peng
	* @since      Framework 1.0
	*/
	protected String sqlDelete() {
		String sql = "DELETE FROM T_CATALOG_SECURITY_FIELD WHERE company_code =? AND user_code=? AND dtl_id=? AND NODE_ID=? ";

		return sql;
	}

	/**
	* Sets the values for a SQL delete statement.
	* @param         pStmt   the incoming prepared statement
	* @param         obj     the incoming VO
	*
	* @author     Saint Peng
	* @since      Framework 1.0
	*/
	protected void setValuesForDelete(PreparedStatement pStmt, Object obj)
		throws SQLException {

		CatalogSecurityFieldVo vo = (CatalogSecurityFieldVo) obj;
		int i = 1;

		pStmt.setString(i++, vo.getCompanyCode());
		pStmt.setString(i++, vo.getUserCode());
		pStmt.setInt(i++, CommonUtil.strToBigDecimal(vo.getFieldDtlId()).intValue());
		pStmt.setInt(i++, CommonUtil.strToBigDecimal(vo.getFieldNodeId()).intValue());
	}

	/**
	* Constructs a string of SQL select statement
	* @return        a string of SQL select statement
	*
	* @author     Saint Peng
	* @since      Framework 1.0
	*/
	protected String sqlFind() {

		String sql ="SELECT A.COMPANY_CODE, A.USER_CODE, A.DTL_ID, A.NODE_ID, A.CREATE_USER, A.CREATE_DATE, "+
		" A.UPDATE_USER,D.REF_DESC,C.DTL_NAME,B.DESCRIPTION, " +
		" A.UPDATE_DATE, A.DC_IND, A.DC_DATE  "+
		" FROM  T_CATALOG_SECURITY_FIELD A,T_CATALOG B,T_CATALOG_DTL C,T_CODE_DTL D "+
		" WHERE A.COMPANY_CODE=B.COMPANY_CODE "+
		" AND A.NODE_ID=B.NODE_ID "+
		" AND A.COMPANY_CODE=C.COMPANY_CODE "+
		" AND A.DTL_ID=C.DTL_ID "+
		" AND C.COMPANY_CODE=D.COMPANY_CODE(+) "+
		" AND C.DTL_TYPE=D.REF_CODE(+)  "+
		" AND D.REF_MASTER_CODE='CATALOG_DTL_TYPE' "+
		" AND D.REF_STATUS='ACTIVE' "+
		" AND A.COMPANY_CODE=? AND A.USER_CODE=? AND  A.DTL_ID =? AND  A.NODE_ID = ? ";

		return sql;
	}

	/**
	* Sets the values for a SQL select statement for finding by primary key.
	* @param         pStmt   the incoming prepared statement
	* @param         obj     the incoming PK
	*
	* @author     Saint Peng
	* @since      Framework 1.0
	*/
	protected void setValuesForFind(PreparedStatement pStmt, Object obj)
		throws SQLException {

		CatalogSecurityFieldPk pk = (CatalogSecurityFieldPk) obj;
		int i = 1;

		pStmt.setString(i++, pk.getCompanyCode());
		pStmt.setString(i++, pk.getUserCode());
		pStmt.setInt(i++, CommonUtil.strToBigDecimal(pk.getFieldDtlId()).intValue());
		pStmt.setInt(i++, CommonUtil.strToBigDecimal(pk.getFieldNodeId()).intValue());
	}

	/**
	* Constructs a string of SQL select statement
	* @return        a string of SQL select statement
	*
	* @author     Saint Peng
	* @since      Framework 1.0
	*/
	protected String sqlList() {
		String sql = "SELECT A.COMPANY_CODE, A.USER_CODE, A.DTL_ID, A.NODE_ID, A.CREATE_USER, A.CREATE_DATE, "+
		" A.UPDATE_USER,D.REF_DESC,C.DTL_NAME,B.DESCRIPTION, " +
		" A.UPDATE_DATE, A.DC_IND, A.DC_DATE  "+
		" FROM  T_CATALOG_SECURITY_FIELD A,T_CATALOG B,T_CATALOG_DTL C,T_CODE_DTL D "+
		" WHERE A.COMPANY_CODE=B.COMPANY_CODE "+
		" AND A.NODE_ID=B.NODE_ID "+
		" AND A.COMPANY_CODE=C.COMPANY_CODE "+
		" AND A.DTL_ID=C.DTL_ID "+
		" AND C.COMPANY_CODE=D.COMPANY_CODE(+) "+
		" AND C.DTL_TYPE=D.REF_CODE(+)  "+
		" AND D.REF_MASTER_CODE='CATALOG_DTL_TYPE' "+
		" AND D.REF_STATUS='ACTIVE' "+
		" AND A.COMPANY_CODE=? "+
		" AND A.USER_CODE=? ";


		/*String sql =  "SELECT SCR_FIELD.USER_ID,"+
		 "        SCR_FIELD.LINE_NO,"+
		 "        SCR_FIELD.ITEM_DETAIL_LINE_NO,"+
		 "        SCR_FIELD.NODE_ID,"+
		 "        SCR_FIELD.CREATE_BY,"+
		 "        SCR_FIELD.CREATE_DATE,"+
		 "        DTL_MAS.DESCRIPTION ITEM_DETAIL_NAME,"+
		 "        CAT_HDR.DESCRIPTION CATALOG_NAME,"+
		 "        CODE_DTL.REF_DESC ITEM_DETAIL_TYPE"+
		 " FROM   T_CATALOG_SECURITY_FIELD SCR_FIELD,"+
		 "        T_USER USR,"+
		 "        T_CATEGORY_ITEM_DETAIL DTL,"+
		 "        T_CATEGORY CAT_HDR,"+
		 "        T_ITEM_DETAIL_MASTER DTL_MAS,"+
		 "        T_CODE_DTL CODE_DTL"+
		 " WHERE  SCR_FIELD.USER_ID = USR.CATALOG_USER_ID"+
		 "        AND USR.COMPANY_CODE = CAT_HDR.CREATE_COMPANY_CODE"+
		 "        AND SCR_FIELD.NODE_ID = CAT_HDR.NODE_ID"+
		 "        AND CAT_HDR.NODE_ID = DTL.NODE_ID"+
		 "        AND SCR_FIELD.ITEM_DETAIL_LINE_NO = DTL.LINE_NO"+
		 "        AND CAT_HDR.CREATE_COMPANY_CODE = DTL_MAS.COMPANY_CODE"+
		 "        AND DTL.ITEM_DETAIL_ID = DTL_MAS.LINE_NO"+
		 "        AND CODE_DTL.COMPANY_CODE = CAT_HDR.CREATE_COMPANY_CODE"+
		 "        AND CODE_DTL.REF_MASTER_CODE = '" + ProjectConstants.CD_ITEM_DETAIL_TYPE + "'"+
		 "        AND CODE_DTL.REF_CODE = DTL_MAS.ITEM_DETAIL_TYPE AND SCR_FIELD.USER_ID = ?";*/
		return sql;
	}

	/**
	* Sets the values for a SQL select statement for finding child tables.
	* @param         pStmt   the incoming prepared statement
	* @param         obj     the incoming parent PK
	*
	* @author     Saint Peng
	* @since      Framework 1.0
	*/
	protected void setValuesForList(PreparedStatement pStmt, Object obj)
		throws SQLException {

		CatalogSecurityHdrPk pk = (CatalogSecurityHdrPk) obj;
		int i = 1;

		pStmt.setString(i++, pk.getCompanyCode());
		pStmt.setString(i++, pk.getUserCode());
	}

	/**
	* Sets the values of a result set to a vo
	* @param         rs   the incoming result set
	* @return        a vo containing the result set values
	*
	* @author     Saint Peng
	* @since      Framework 1.0
	*/
	protected SimpleBaseVo getResultSetValues(ResultSet rs) throws SQLException {

		CatalogSecurityFieldVo vo = new CatalogSecurityFieldVo();

		vo.setCompanyCode(rs.getString("COMPANY_CODE"));
		vo.setUserCode(rs.getString("USER_CODE"));
		vo.setFieldDtlId(CommonUtil.bigDecimalToStr(rs.getBigDecimal("DTL_ID")));
		vo.setFieldNodeId(CommonUtil.bigDecimalToStr(rs.getBigDecimal("NODE_ID")));
		vo.setCreateUser(rs.getString("CREATE_USER"));
		vo.setCreateDate(rs.getTimestamp("CREATE_DATE"));
		vo.setUpdateUser(rs.getString("UPDATE_USER"));
		vo.setUpdateDate(rs.getTimestamp("UPDATE_DATE"));
		vo.setFieldCatalogName(rs.getString("DESCRIPTION"));
		vo.setFieldItemDetailName(rs.getString("DTL_NAME"));
		vo.setFieldItemDetailType(rs.getString("REF_DESC"));


		return vo;
	}

}
